package com.h5ve.toolkit.service.impl;

import cn.dev33.satoken.stp.StpUtil;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.h5ve.base.api.R;
import com.h5ve.base.constant.CommonConstants;
import com.h5ve.base.constant.GenConstants;
import com.h5ve.base.util.DateUtil;
import com.h5ve.base.util.StrUtil;
import com.h5ve.security.entity.H5veUser;
import com.h5ve.security.util.SecurityUtil;
import com.h5ve.system.entity.DictData;
import com.h5ve.system.service.DeptService;
import com.h5ve.system.service.DictDataService;
import com.h5ve.toolkit.dto.FormDto;
import com.h5ve.toolkit.entity.Column;
import com.h5ve.toolkit.entity.Form;
import com.h5ve.toolkit.entity.Table;
import com.h5ve.toolkit.mapper.FormMapper;
import com.h5ve.toolkit.service.ColumnService;
import com.h5ve.toolkit.service.FormService;
import com.h5ve.toolkit.service.TableService;
import com.h5ve.toolkit.util.BuilderUtil;
import lombok.AllArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

import static cn.hutool.core.date.DateUtil.formatDateTime;

/**
 * @author h5ve
 * @date 2021-03-11 21:57:03
 * @description 表单管理Service业务层
 */
@Service
@AllArgsConstructor
public class FormServiceImpl extends ServiceImpl<FormMapper, Form> implements FormService {

    private final JdbcTemplate jdbcTemplate;
    private final TableService tableService;
    private final ColumnService columnService;
    private final DeptService deptService;
    private final DictDataService dictDataService;

    @Override
    public Page<Form> customFormPage(Page<Form> page, Form form) {
        return page.setRecords(baseMapper.selectCustomQuery(page, form));
    }

    /**
     * 动态表单数据1（不带分页）
     *
     * @param form
     * @return
     */
    @Override
    public List<Map<String, Object>> mapFormList(Form form) {
        Form form1 = baseMapper.selectOne(new QueryWrapper<Form>().eq("code", form.getCode()).eq("is_main", "1"));
        String fields = "";
        String search = " where del_flag = 0 ";
        List<Column> columns = BuilderUtil.createColumns(form1.getData(), form1.getType());
        for (Column column : columns) {
            if ("1".equals(column.getIsList()) || "parent_id".equals(column.getColumnName()) && "1".equals(form.getType())) {
                fields += column.getColumnName() + ",";
            }
        }

        if (CharSequenceUtil.isNotBlank(fields)) {
            fields = fields.substring(0, fields.length() - 1);
        }

        //查询条件构建
        search += buidleSearch(form, columns);

        String sql = "select " + fields + " from " + CommonConstants.SQLPREFIX + form1.getTableName() + search;
        log.error("mapFormList sql: " + sql);
        //todo 构建条件查询sql
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    @Override
    public Page<Map<String, Object>> mapFormPage(Page page, Form form) {
        // 查询到当前表单
        Form form1 = baseMapper.selectOne(new QueryWrapper<Form>().eq("code", form.getCode()).eq("is_main", "1"));
        String fields = "";
        String search = " where del_flag = 0 ";

        //拿到所有列数据
        List<Column> columns = columnService.selectColumnsByName("form_" + form1.getTableName());

        for (Column column : columns) {
            if ("1".equals(column.getIsList())) {
                fields += column.getColumnName() + ",";
            }
            //添加查询字段： 创建人
            if ("create_by".equals(column.getColumnName())) {
                fields += column.getColumnName() + ",";
            }
            if ("create_time".equals(column.getColumnName())) {
                fields += column.getColumnName() + ",";
            }
        }

        if (CharSequenceUtil.isNotBlank(fields)) {
            fields = fields.substring(0, fields.length() - 1);
        }

        String pageSql = "";//sql
        String whereSql = "";
        search += buidleSearch(form, columns);
        if (!whereSql.equals("")) {
            int total = jdbcTemplate.queryForObject("select count(1) from " + "( select " + fields + " from " + CommonConstants.SQLPREFIX + form1.getTableName() + search + " ORDER BY create_time DESC ) as a " + whereSql, Integer.class);
            page.setTotal(total);

            //查询对应库表里是否存在
            List<String> queryForColumList = jdbcTemplate
                    .queryForList("SELECT DISTINCT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = '"
                            + CommonConstants.SQLPREFIX + form1.getTableName() + "'" + " and column_name = 'sort'", String.class);
            String orderSort = "";
            if (ObjectUtil.isAllNotEmpty(queryForColumList)) {
                if (queryForColumList.contains("sort")) {
                    orderSort += ",sort asc, id asc";
                }
            }

            pageSql = "select " + fields + " from " + CommonConstants.SQLPREFIX + form1.getTableName() + search + " ORDER BY create_time DESC " + orderSort;

            pageSql = "select  a.* from (" + pageSql + ") as a " + whereSql + " limit " + (page.getCurrent() - 1) * page.getSize() + "," + page.getSize();
            log.debug("mapFormPage pageSql: " + pageSql);
        } else {
            int total = jdbcTemplate.queryForObject("select count(1) from " + CommonConstants.SQLPREFIX + form1.getTableName() + search, Integer.class);
            page.setTotal(total);

            //查询对应库表里是否存在
            List<String> queryForColumList = jdbcTemplate
                    .queryForList("SELECT DISTINCT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = '"
                            + CommonConstants.SQLPREFIX + form1.getTableName() + "'" + " and column_name = 'sort'", String.class);
            String orderSort = "";
            if (ObjectUtil.isAllNotEmpty(queryForColumList)) {
                if (queryForColumList.contains("sort")) {
                    orderSort += ",sort asc, id asc";
                }
            }

            pageSql = "select " + fields + " from " + CommonConstants.SQLPREFIX + form1.getTableName() + search + " ORDER BY create_time DESC " + orderSort + " limit " + (page.getCurrent() - 1) * page.getSize() + "," + page.getSize();
            log.debug("mapFormPage pageSql: " + pageSql);


        }

        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(pageSql);
        page.setRecords(mapList);
        // 添加日志
        /*OperLog operLog = new OperLog();
        operLog.setType(Integer.valueOf(LogTypeEnum.SELECT.getValue()));
        operLog.setTitle("动态表单查询");
        operLog.setMethod("POST");
        operLog.setOperName(StpUtil.getLoginId() + "");
        operLog.setClientId("ps");
        operLog.setOperUrl("/toolkit/form/dynamicFormList");
        operLog.setOperParam(pageSql);
        operLog.setOperTime(new Date());
        logService.saveLog(operLog);*/
        return page;
    }

    @Override
    public Map queryData(String tableName, Integer id) {
        String originalSql = "select * from " + CommonConstants.PREFIX + tableName + " where id = '" + id + "'";
        Map map = jdbcTemplate.queryForMap(originalSql);
        return map;
    }

    /**
     * 数据查询
     *
     * @param tableName
     * @return
     */
    @Override
    public Map queryData(String tableName, Integer id, String type) {
        if (CharSequenceUtil.isNotEmpty(tableName) && id != null) {
            Table table = getTable(tableName);
            String columnsSql = BuilderUtil.getColumns(table, "1");
            String originalSql = "select " + columnsSql + " from " + CommonConstants.SQLPREFIX + tableName + " where id = '" + id + "'";
            Map map = jdbcTemplate.queryForMap(originalSql);
            if ("2".equals(type)) {  //主子表
                if (CharSequenceUtil.isNotEmpty(table.getChildTable()) && !"[]".equals(table.getChildTable())) {
                    JSONArray jsonArray = JSONUtil.parseArray(table.getChildTable());
                    List<Map<String, Object>> children = new ArrayList<>();
                    for (int i = 0; i < jsonArray.size(); i++) {
                        JSONObject jsonObject = jsonArray.getJSONObject(i);
                        String tableName1 = jsonObject.getStr("tableName");
                        String foreignKey = jsonObject.getStr("foreignKey");
                        tableName1 = tableName1.contains(CommonConstants.SQLPREFIX) ? tableName1 : CommonConstants.SQLPREFIX + tableName1;

                        Table table1 = getTable(tableName1);
                        String columnStr = BuilderUtil.getColumns(table1, "1");
                        //查询子表数据
                        if (CharSequenceUtil.isNotEmpty(tableName1) && CharSequenceUtil.isNotEmpty(columnStr) && CharSequenceUtil.isNotEmpty(foreignKey)) {
                            String sql = "select " + columnStr + " from " + tableName1 + " where " + foreignKey + " = '" + id + "'";
                            Map<String, Object> mapChild = new HashMap<>();
                            mapChild.put("tableName", tableName1);
                            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
                            if (list != null && list.size() > 0) {
                                mapChild.put("data", list);
                            }
                            children.add(mapChild);
                        }
                    }
                    map.put("children", children);  //设置子表
                }
            }
            return map;
        }
        return null;
    }

    /**
     * 数据处理
     *
     * @param tableName
     * @return R
     */
    private Table getTable(String tableName) {
        tableName = tableName.contains(CommonConstants.SQLPREFIX) ? tableName : CommonConstants.SQLPREFIX + tableName;
        Table table = tableService.getOne(new QueryWrapper<Table>().eq("table_name", tableName));
        List<Column> columns = columnService.list(new QueryWrapper<Column>().eq("table_id", table.getId()));
        table.setColumns(columns);
        return table;
    }

    /**
     * 构建完整查询条件
     *
     * @param form    表单信息  data查询条件
     * @param columns 数据表所有字段
     * @return
     */
    private String buidleSearch(Form form, List<Column> columns) {
        StringBuilder search = new StringBuilder();
        //特殊处理
        StringBuilder searchs = new StringBuilder();
        if (CharSequenceUtil.isNotBlank(form.getData()) && !CharSequenceUtil.equals("{}", form.getData())) {
            JSONObject jsonObject = JSONUtil.parseObj(form.getData());
            columns.forEach(column -> {
                String value = jsonObject.getStr(column.getColumnName());
                if (CharSequenceUtil.isNotBlank(value)) {
                    //判断是否是下拉框 下拉框单独处理
                    if (column.getHtmlType().equals("select")) {
                        List<DictData> label = dictDataService.list(new QueryWrapper<DictData>().like("label", value).eq("dict_type", column.getDictType()));
                        String val = "";
                        for (DictData pd : label) {
                            if (val.equals("")) {
                                val = pd.getValue();
                            } else {
                                val += "," + pd.getValue();
                            }
                        }
                        if (CharSequenceUtil.isNotBlank(val)) {
                            search.append(" and ").append(column.getColumnName()).append(" ").append(queryType("in", val));
                        } else {
                            search.append(" and ").append(column.getColumnName()).append(" ").append(queryType("in", value + "无数据"));
                        }

                    } else {
                        search.append(" and ").append(column.getColumnName()).append(" ").append(queryType(column.getQueryType(), value));
                    }
                }
            });
        }

        /*String searchStr = search.toString();
        if (b) {
            if (CharSequenceUtil.isNotBlank(sField) && !CharSequenceUtil.containsAny(searchStr, sField)) {
                search.append(" and ").append(sField).append(" IN (SELECT d.code FROM sys_user_dept ud, sys_dept d WHERE ud.dept_id = d.dept_id  AND ud.is_main = '1' AND d.type = 0 AND ud.user_id = '").append(SecurityUtil.getUser().getId()).append("')");
            }
        }

        if (CharSequenceUtil.isNotBlank(cField) && !CharSequenceUtil.containsAny(searchStr, cField)) {
            List<Map<String, Object>> queryForList = jdbcTemplate.queryForList("SELECT d.code FROM sys_user_dept ud, sys_dept d WHERE ud.dept_id = d.dept_id  AND ud.is_main = '0' AND d.type = 1 AND ud.user_id = '" + SecurityUtil.getUser().getId() + "' ");
            StringBuilder dcode = new StringBuilder();
            for (Map<String, Object> map : queryForList) {
                dcode.append(map.get("code")).append(",");
            }
            if (CharSequenceUtil.isNotBlank(dcode.toString())) {
                dcode = new StringBuilder(dcode.substring(0, dcode.length() - 1));

                search.append(" and ").append(cField).append(" IN (").append(dcode).append(")");

            }
        }*/
        return search.toString();
    }

    @Override
    public void saveData(Form form, List<Column> columns, String data) {
        JSONObject jData = JSONUtil.parseObj(data);
        String insertSql = "insert into " + CommonConstants.PREFIX + form.getTableName() + "(";
        String insertSqlValue = " values(";
        H5veUser user = SecurityUtil.getUser();
        for (Column column : columns) {
            if (!StrUtil.containsAny(column.getColumnName(), GenConstants.COLUMNNAME_NOT_EDIT) && StrUtil.equals(column.getIsAdd(), "1")) {
                insertSql += column.getColumnName() + ",";
                if (StrUtil.endWithAny(column.getJavaType(), "Integer", "Long")) {
                    insertSqlValue += "" + jData.get(column.getJavaField()) + ",";
                } else {
                    insertSqlValue += "'" + jData.get(column.getJavaField()) + "',";
                }
            }
            if (StrUtil.equals(column.getColumnName(), "create_by") && user != null) {
                insertSql += "create_by,";
                insertSqlValue += "'" + user.getUserName() + "',";
            }
            if (StrUtil.equals(column.getColumnName(), "create_time")) {
                insertSql += "create_time,";
                insertSqlValue += "'" + DateUtil.formatDateTime(new Date()) + "',";
            }
        }
        insertSql = insertSql.substring(0, insertSql.length() - 1);
        insertSqlValue = insertSqlValue.substring(0, insertSqlValue.length() - 1);
        insertSql += ")" + insertSqlValue + ")";
        jdbcTemplate.execute(insertSql);
    }

    /**
     * 动态表单数据新增
     *
     * @param formDto
     * @return
     */
    @Override
    public String saveData(Form form, FormDto formDto) {
        //当动态表单信息不为空的时候提取出所有字段信息
        List<Column> columns = new ArrayList<>();
        if (!ObjectUtil.equals(null, form.getData()) && !"".equals(form.getData())) {
            columns = BuilderUtil.createColumns(form.getData(), form.getType());
        }
        //判断数据在数据库中是否存在
        /*String msg = estimateRepetition(form, columns, formDto.getData());
        if (!Objects.equals(msg, "true")) {
            return msg;
        }*/

        //数据入库
        String insertSql = getInsertSql(form.getTableName(), columns, formDto.getData(), null);

        if ("0".equals(form.getType())) {  //单表
            jdbcTemplate.execute(insertSql);
        } else if ("1".equals(form.getType())) {  //树表
//            //保存树表数据 并获取主键
//            KeyHolder keyHolder=new GeneratedKeyHolder();
//            jdbcTemplate.update(new PreparedStatementCreator() {
//                @Override
//                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
//                    return con.prepareStatement(insertSql,Statement.RETURN_GENERATED_KEYS);
//                }
//            },keyHolder);
//
//            //根据主键查询刚新增的值，并拼接parent_id
//            Number key1 = keyHolder.getKey();
            jdbcTemplate.execute(insertSql);

        } else {  //主子表

            //设置主键
            KeyHolder keyHolder = new GeneratedKeyHolder();
            String sql = insertSql;
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                    return con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                }
            }, keyHolder);
            //获取主键值
            Number key = keyHolder.getKey();

            //保存子表数据
            if (CharSequenceUtil.isNotEmpty(formDto.getChildren())) {
                Table table = tableService.getOne(new QueryWrapper<Table>().eq("table_name", CommonConstants.SQLPREFIX + form.getTableName()));
                if (table != null) {
                    JSONArray array = JSONUtil.parseArray(formDto.getChildren());
                    for (int i = 0; i < array.size(); i++) {
                        JSONObject obj = array.getJSONObject(i);
                        String tableName = obj.getStr("tableName");
                        JSONArray jsonArray = JSONUtil.parseArray(obj.getStr("data"));
                        tableName = tableName.contains("form_") ? tableName : CommonConstants.SQLPREFIX + tableName;

                        Table table1 = tableService.getOne(new QueryWrapper<Table>().eq("table_name", tableName));

                        //查找关联外键名
                        String childTable = table.getChildTable();
                        String foreignKey = "";
                        if (CharSequenceUtil.isNotEmpty(childTable) && !"[]".equals(childTable)) {
                            JSONArray arr = JSONUtil.parseArray(childTable);
                            for (int k = 0; k < arr.size(); k++) {
                                JSONObject jo = arr.getJSONObject(k);
                                String tableName1 = jo.getStr("tableName");
                                tableName1 = tableName1.contains("form_") ? tableName1 : CommonConstants.SQLPREFIX + tableName1;
                                if (tableName1.equals(tableName)) {
                                    foreignKey = jo.getStr("foreignKey");
                                    break;
                                }
                            }
                        }
                        //查找列
                        List<Column> columns1 = columnService.list(new QueryWrapper<Column>().eq("table_id", table1.getId()));
                        if (columns1.size() > 0 && CharSequenceUtil.isNotEmpty(foreignKey)) {
                            String result = "";
                            for (int k = 0; k < jsonArray.size(); k++) {
                                JSONObject job = jsonArray.getJSONObject(k);
                                job.set(foreignKey, key);  //设置外键值
                                result += (getInsertSql(tableName, columns1, job.toString(), foreignKey) + ";");
                            }
                            if (CharSequenceUtil.isNotEmpty(result)) {
                                jdbcTemplate.execute(result);
                            }
                        }
                    }
                }
            }
        }

        return "true";
    }

    /**
     * 动态表单数据修改
     *
     * @param form
     * @return
     */
    @Override
    public R updateData(Form form) {
        String data = form.getData();
        //获取表单列数据
        Form form1 = baseMapper.selectOne(new QueryWrapper<Form>().eq("code", form.getCode()).eq("is_main", 1));
        form.setTableName(form1.getTableName());
        Table table = BuilderUtil.createForm(form1);
        List<Column> columns = table.getColumns();
        JSONObject jData = JSONUtil.parseObj(data);

        String updateSql = "update " + CommonConstants.SQLPREFIX + form1.getTableName() + " SET ";
        String updateSqlWhere = " where id = " + form.getId();
        H5veUser user = SecurityUtil.getUser();
        Map m = new HashMap();
        Map m1 = new HashMap();
        String parentId = jData.getStr("parent_id");
        if ("1".equals(form1.getType()) && form1 != null) {
            //原始数据 用来判断是否修改了父ID
            m = jdbcTemplate.queryForMap("select id,parent_id,ancestors from " + CommonConstants.SQLPREFIX + form1.getTableName() + updateSqlWhere);
            //修改到那个父节点对象下
            if (CharSequenceUtil.isNotEmpty(parentId)) {
                m1 = jdbcTemplate.queryForMap("select id,parent_id,ancestors from " + CommonConstants.SQLPREFIX + form1.getTableName() + " where id=" + parentId);
            }
        }
        if (columns.size() > 0) {
            for (Column column : columns) {
                if (!CharSequenceUtil.containsAny(column.getColumnName(), GenConstants.COLUMNNAME_NOT_EDIT) && CharSequenceUtil.equals(column.getIsAdd(), "1")) {
                    if (CharSequenceUtil.endWithAny(column.getJavaType(), "Integer", "Long", "BigDecimal")) {
                        String sql = Objects.equals(jData.get(column.getColumnName()), null) || Objects.equals(jData.get(column.getColumnName()), "") ? 0 + "'," : jData.get(column.getColumnName()) + "',";

                        updateSql += column.getColumnName() + " = '" + sql;
                    } else {
                        String str = jData.getStr(column.getColumnName());

                        updateSql += column.getColumnName() + " = " + (CharSequenceUtil.isNotEmpty(str) ? "'" + str + "'," : "null,");
                    }
                }
                if (CharSequenceUtil.equals(column.getColumnName(), "update_by") && user != null) {
                    updateSql += "update_by = '" + user.getUserName() + "',";
                }
                if (CharSequenceUtil.equals(column.getColumnName(), "update_time")) {
                    updateSql += "update_time = '" + formatDateTime(new Date()) + "',";
                }
                //当列名为parent_id 并且 当前节点的parent_id和传入的parent_id值不同时
                if ("1".equals(form1.getType()) && CharSequenceUtil.equals(column.getColumnName(), "parent_id") && !Objects.equals(m.get("parent_id"), jData.get("parent_id"))) {
                    updateSql += "parent_id = " + parentId + ",";
                    if (CharSequenceUtil.isNotEmpty(parentId)) {
                        updateSql += "ancestors = '" + (ObjectUtil.isEmpty(m1.get("ancestors")) ? parentId : m1.get("ancestors") + "," + parentId) + "',";
                    } else {
                        updateSql += "ancestors=null,";
                    }
                }
            }
        }
        updateSql = updateSql.substring(0, updateSql.length() - 1);
        updateSql += updateSqlWhere;

        jdbcTemplate.execute(updateSql);

        //修改子表数据
        if (form1 != null && "2".equals(form1.getType())) {
            String childForm = jData.getStr("childForm");
            String id = jData.getStr("id");

            if (CharSequenceUtil.isNotEmpty(childForm)) {
                //获取主表的子表json串
                Table ta = tableService.getOne(new QueryWrapper<Table>().eq("table_name", CommonConstants.SQLPREFIX + form1.getTableName()));
                String ct = ta.getChildTable();

                JSONArray arr = JSONUtil.parseArray(childForm);
                for (int i = 0; i < arr.size(); i++) {
                    JSONObject obj = arr.getJSONObject(i);
                    String tableName = obj.getStr("tableName");
                    tableName = tableName.contains(CommonConstants.SQLPREFIX) ? tableName : CommonConstants.SQLPREFIX + tableName;
                    Table t1 = getTable(tableName);

                    //获取外键值
                    String foreignKey = "";
                    if (CharSequenceUtil.isNotEmpty(ct)) {
                        JSONArray jsonArray = JSONUtil.parseArray(ct);
                        for (int k = 0; k < jsonArray.size(); k++) {
                            JSONObject jsonObject = jsonArray.getJSONObject(k);
                            String tableName1 = jsonObject.getStr("tableName");
                            tableName1 = tableName1.contains(CommonConstants.SQLPREFIX) ? tableName1 : CommonConstants.SQLPREFIX + tableName1;
                            if (tableName.equals(tableName1)) {
                                foreignKey = jsonObject.getStr("foreignKey");
                                obj.set("foreignKey", foreignKey);
                                obj.set("foreignValue", id);
                                break;
                            }
                        }

                        //修改子表数据
                        String updateChild = BuilderUtil.getUpdateSql(t1, obj);
                        if (CharSequenceUtil.isNotEmpty(updateChild)) {
                            jdbcTemplate.execute(updateChild);
                        }
                    }
                }
            }
        }
        return R.ok("修改成功");
    }

    /**
     * 审查查询sql语句
     *
     * @param tableName
     * @return
     */
    private String getInsertSql(String tableName, List<Column> columns, String data, String foreignKey) {
        //获取表名称
        tableName = tableName.contains("form_") ? tableName : CommonConstants.SQLPREFIX + tableName;
        //查询表单数据
        Table table = tableService.getOne(new QueryWrapper<Table>().eq("table_name", tableName));
        //判断表是否存在
        if (ObjectUtil.isNotEmpty(table)) {

            JSONObject jData = JSONUtil.parseObj(data);
            String insertSql = "insert into " + tableName + "(";
            String insertSqlValue = " values(";

            //当表单字段属性部位空的时候
            if (columns.size() > 0) {
                for (Column column : columns) {
                    if (!CharSequenceUtil.containsAny(column.getColumnName(), GenConstants.COLUMNNAME_NOT_EDIT) && (CharSequenceUtil.equals(column.getIsAdd(), "1") || CharSequenceUtil.isNotEmpty(foreignKey) && column.getColumnName().equals(foreignKey))) {
                        insertSql += column.getColumnName() + ",";
                        if (CharSequenceUtil.endWithAny(column.getJavaType(), "Integer", "Long")) {
                            insertSqlValue += "" + jData.get(column.getColumnName()) + ",";
                        } else {
                            insertSqlValue += null == jData.get(column.getColumnName()) ? null + "," : "'" + jData.get(column.getColumnName()) + "',";
                        }
                    }
                    if (CharSequenceUtil.equals(column.getColumnName(), "create_by")) {
                        insertSql += "create_by,";
                        // 兼容流程
                        String createBy = jData.getStr("create_by");
                        if (StrUtil.isNotBlank(createBy)) {
                            insertSqlValue += "'" + createBy + "',";
                        } else {
                            insertSqlValue += "'" + StpUtil.getLoginId() + "',";
                        }
                    }
                    if (CharSequenceUtil.equals(column.getColumnName(), "data_from")) {
                        insertSql += "data_from,";
                        // 兼容流程
                        String dataFrom = jData.getStr("data_from");
                        if (StrUtil.isNotBlank(dataFrom)) {
                            insertSqlValue += "'" + dataFrom + "',";
                        } else {
                            insertSqlValue += "'" + 0 + "',";
                        }
                    }
                    if (CharSequenceUtil.equals(column.getColumnName(), "create_time")) {
                        insertSql += "create_time,";
                        // 兼容流程
                        String createTime = jData.getStr("create_time");
                        if (StrUtil.isNotBlank(createTime)) {
                            Date date = new Date(Long.parseLong(createTime));
                            insertSqlValue += "'" + formatDateTime(date) + "',";
                        } else {
                            insertSqlValue += "'" + formatDateTime(new Date()) + "',";
                        }
                    }
                    if (CharSequenceUtil.equals(column.getColumnName(), "parent_id")) {
                        if (GenConstants.TPL_TREE.equals(table.getTplCategory())) {
                            insertSql += "parent_id,";
                            insertSqlValue += (CharSequenceUtil.isEmpty(jData.getStr("parent_id")) ? "null" : jData.getStr("parent_id")) + ",";

                            //设置 父ID集合的值
                            String parentId = jData.getStr("parent_id");
                            if (CharSequenceUtil.isNotEmpty(parentId)) {
                                Map<String, Object> map = jdbcTemplate.queryForMap("select ancestors from " + tableName + " where id=" + parentId);
                                String ancestors = ObjectUtil.isEmpty(map.get("ancestors")) ? parentId + "" : map.get("ancestors") + "," + parentId;
                                insertSql += "ancestors,";
                                insertSqlValue += "'" + ancestors + "',";
                            }
                        }
                    }
                }
            }
            insertSql = insertSql.substring(0, insertSql.length() - 1);
            insertSqlValue = insertSqlValue.substring(0, insertSqlValue.length() - 1);
            insertSql += ")" + insertSqlValue + ")";
            return insertSql;
        }
        return "";
    }

    public String estimateRepetition(Form form, List<Column> columns, String data) {
        //获取表名称
        String tableName = form.getTableName().contains("form_") ? form.getTableName() : CommonConstants.SQLPREFIX + form.getTableName();
        //查询表单数据
        Table table = tableService.getOne(new QueryWrapper<Table>().eq("table_name", tableName));
        //判断表是否存在
        if (ObjectUtil.isNotEmpty(table)) {

            JSONObject jData = JSONUtil.parseObj(data);
            //生效日期 与 失效日期不参与重复验证
            //所有导入的字段
            List<Column> columnList = columns.stream()
                    .filter(a -> "1".equals(a.getIsImport()))
                    .collect(Collectors.toList());
            //查询条件
            String selectSql = "select * from " + tableName + " where ";
            String selectSqlValue = " del_flag = 0 ";

            List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectSql + selectSqlValue);
            if (maps.size() > 0) {
                return "当前数据已存在！";
            }

        }
        return "true";
    }

    @Override
    public void removeData(String tableName, Integer[] ids) {
        String deleteSql = "delete from " + CommonConstants.PREFIX + tableName + " where id in ( ";
        String idArra = "";
        for (Integer id : ids) {
            idArra = idArra + "'" + id + "',";
        }
        idArra = idArra.substring(0, idArra.length() - 1);
        deleteSql = deleteSql + idArra + " ) ";
        jdbcTemplate.execute(deleteSql);
    }

    /**
     * 根据选择的条件来匹配对应的比较运算符
     *
     * @param type  设置的条件
     * @param value 条件值
     * @return 输出拼接后的条件
     */
    private String queryType(String type, String value) {
        String result = "";
        switch (type) {
            case "eq":  //等于
                result = " = '" + value + "'";
                break;
            case "like": //模糊匹配
                result = " like \"%" + value + "%\"";
                break;
            case "ne":  //不等于
                result = " <> '" + value + "'";
                break;
            case "gt":  //大于
                result = " > " + value;
                break;
            case "ge":  //大于等于
                result = " >= " + value;
                break;
            case "lt":  //小于
                result = " < " + value;
                break;
            case "le":  //小于等于
                result = " <= " + value;
                break;
            case "in":  //范围查询
                String sql = "";
                value = value.replace(" ", "");
                String[] split = value.split(",");
                for (String s : split) {
                    sql += "'" + s + "',";
                }
                sql = sql.substring(0, sql.length() - 1);
                result = " in " + " (" + sql + ")";
                break;
            case "isNull":  //为空
                result = " isNull ";
                break;
            case "isNotNull":   //不为空
                result = " isNotNull ";
                break;
            default:
                result = "";
                break;
        }
        return result;
    }
}
